﻿/*===============================================================================
Old Name: MTSL_View_PerfmonInstanceNamesAndIds

LoadTestRunId	InstanceId	InstanceName									MachineName		CategoryName	CounterName			Instance
11				0			\\CONTROLLER\Memory\Page Faults/sec				CONTROLLER		Memory			Page Faults/sec	
11				390			\\CONTROLLER\Process(QTAgent32)\Thread Count	CONTROLLER		Process			Thread Count		QTAgent32
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PerfmonInstanceNamesAndIds AS
	---------------------------------------------------------------------------------
		SELECT 
			instance.LoadTestRunId
			,instance.InstanceId
			,(
				'\\' + category.MachineName
				+ '\' + category.CategoryName
				+	case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						 then ''
						 else '(' + instance.InstanceName  + ')'
						 end
				+ '\' + counter.CounterName
			) AS InstanceName
			,category.MachineName
			,category.CategoryName
			,counter.CounterName
			,(case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						 then ''
						 else instance.InstanceName
						 end) AS Instance
			,instance.LoadTestItemId

		FROM LoadTestPerformanceCounterCategory AS category 

		INNER JOIN LoadTestPerformanceCounter AS counter 
			ON category.LoadTestRunId = counter.LoadTestRunId
			AND category.CounterCategoryId = counter.CounterCategoryId

		INNER JOIN LoadTestPerformanceCounterInstance AS instance 
			ON counter.CounterId = instance.CounterId
			AND counter.LoadTestRunId = instance.LoadTestRunId
	GO 
/*===============================================================================
Old Name: TSL_View_PageNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				RequestId	RequestUri				RequestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	18			ReportServer-Homepage1	ReportServer-Homepage1(018)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PageNamesWithIDs AS
		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName, RequestId, RequestUri 
			  ,RequestUri + '(' + RIGHT ('000'+ CAST(RequestId AS nvarchar(3)), 3) + ')' AS RequestNameWithId
		FROM dbo.WebLoadTestRequestMap AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
TSL_View_TestNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TestNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
12				0			Scenario1		0			Single Project_ViewAll_OFF	Single Project_ViewAll_OFF(000)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_TestNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName 
			  ,TestCaseName + '(' + RIGHT ('000'+ CAST(TestCaseId AS nvarchar(3)), 3) + ')' AS TestNameWithId
		FROM LoadTestCase AS a

		INNER JOIN LoadTestScenario AS c 
			ON a.LoadTestRunId = c.LoadTestRunId AND a.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
VSLT_View_TransactionNamesWithIDs

LoadTestRunId	ScenarioId	ScenarioName	TestCaseId	TestCaseName				TransactionId	TransactionName	TransactionNameWithId
11				0			Scenario1		0			Single Project_ViewAll_OFF	0				HomePage		HomePage(000)
11				0			Scenario1		0			Single Project_ViewAll_OFF	1				Expand1			Expand1(001)
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_TransactionNamesWithIDs AS

		SELECT a.LoadTestRunId, c.ScenarioId, ScenarioName, a.TestCaseId, TestCaseName, TransactionId, TransactionName 
			  ,[TransactionName] + '(' + RIGHT ('000'+ CAST([TransactionId] AS nvarchar(3)), 3) + ')' AS TransactionNameWithId
		FROM [WebLoadTestTransaction] AS a

		INNER JOIN LoadTestCase AS b 
			ON a.LoadTestRunId = b.LoadTestRunId AND a.TestCaseId = b.TestCaseId

		INNER JOIN LoadTestScenario AS c 
			ON b.LoadTestRunId = c.LoadTestRunId AND b.ScenarioId = c.ScenarioId
	GO
/*===============================================================================
VSLT_View_PageSummary
Pulls data from the LoadTestPageSummaryData table and shows it with the proper name resolutions
NOTE: This data is only accurate for runs where the user load remained constant throughout the load.

LoadTestRunId	PageId	TestCaseId	ScenarioId	ScenarioName	TestCaseName		RequestUri		PageCount	Average	Minimum	Maximum	Percentile90	Percentile95	Percentile99	Median	StandardDeviation
1				7		0			0			Scenario1		PhoeneticAlphabet	http://usmi..	12			3.28667	1.066	3.826	3.793			3.826			3.826			3.705	0.959920424942725
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_PageSummary AS
	SELECT 
		pageSummaryData.LoadTestRunId
		,pageSummaryData.PageId
		,testCase.TestCaseId
		,scenario.ScenarioId
		,scenario.ScenarioName
		,testCase.TestCaseName
		,requestMap.RequestUri
		, pageSummaryData.PageCount
		, pageSummaryData.Average
		, pageSummaryData.Minimum
		, pageSummaryData.Maximum
		, pageSummaryData.Percentile90
		, pageSummaryData.Percentile95
		, pageSummaryData.Percentile99
		, pageSummaryData.Median
		, pageSummaryData.StandardDeviation 
		, run.RunDuration
	FROM LoadTestPageSummaryData AS pageSummaryData

	INNER JOIN WebLoadTestRequestMap AS requestMap 
		ON pageSummaryData.LoadTestRunId = requestMap.LoadTestRunId
		AND pageSummaryData.PageId = requestMap.RequestId

	INNER JOIN LoadTestCase AS testCase 
		ON pageSummaryData.LoadTestRunId = testCase.LoadTestRunId 
		AND requestMap.TestCaseId = testCase.TestCaseId

	INNER JOIN LoadTestScenario AS scenario 
		ON pageSummaryData.LoadTestRunId = scenario.LoadTestRunId 
		AND testCase.ScenarioId = scenario.ScenarioId

	INNER JOIN LoadTestRun as run
		ON pageSummaryData.LoadTestRunId = run.LoadTestRunId
	GO
/*===============================================================================
TSL_View_TransactionSummary
Pulls data from the LoadTestTransactionSummaryData table and shows it with the proper name resolutions
LoadTestRunId	TransactionId	TestCaseId	ScenarioId	ScenarioName	TestCaseName		TransactionName	TransactionCount	Average	Minimum	Maximum	Percentile90	Percentile95	Percentile99	Median	StandardDeviation
3				0				0			0			Scenario1		PhoeneticAlphabet	Timer1			244					1.00587	0.326	13.753	1.2				1.291			9.335			1.024	1.34988881667632
===============================================================================*/
	--START CODE--
	CREATE VIEW VSLT_View_TransactionSummary AS
	SELECT 
		transSummaryData.LoadTestRunId
		,transSummaryData.TransactionId
		,testCase.TestCaseId
		,scenario.ScenarioId
		,scenario.ScenarioName
		,testCase.TestCaseName
		,requestMap.TransactionName
		, transSummaryData.TransactionCount
		, transSummaryData.Average
		, transSummaryData.Minimum
		, transSummaryData.Maximum
		, transSummaryData.Percentile90
		, transSummaryData.Percentile95
		, transSummaryData.Percentile99
		, transSummaryData.Median
		, transSummaryData.StandardDeviation  
	FROM LoadTestTransactionSummaryData AS transSummaryData

	INNER JOIN WebLoadTestTransaction AS requestMap 
		ON transSummaryData.LoadTestRunId = requestMap.LoadTestRunId
		AND transSummaryData.TransactionId = requestMap.TransactionId

	INNER JOIN LoadTestCase AS testCase 
		ON transSummaryData.LoadTestRunId = testCase.LoadTestRunId 
		AND requestMap.TestCaseId = testCase.TestCaseId

	INNER JOIN LoadTestScenario AS scenario 
		ON transSummaryData.LoadTestRunId = scenario.LoadTestRunId 
		AND testCase.ScenarioId = scenario.ScenarioId
	GO